package cn.jhc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cn.jhc.bean.AllHfmsgBean;
import cn.jhc.bean.HfmsgBean;
import cn.jhc.bean.MailBean;

public class MailDAO {

	private Connection con = null;
	private int mailNum;
	private int PageCount;
	
	public void addMail(int fromUserid, String toname, String fromContent) {
		String sql = "insert into mail(fromUserid,toname,fromContent,reading,date) "
				+ "values(?,?,?,'no',now())";
		PreparedStatement st = null;
		try {
			con = ConnectionFactory.getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, fromUserid);
			st.setString(2, toname);
			st.setString(3, fromContent);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public ArrayList<MailBean> getHaveRead(String toname,int page,int titleCount) {
		ArrayList<MailBean> list = new ArrayList<MailBean>();
		String sql = "select a.id,u.id,u.name,u.displayname,a.fromContent,a.toname,a.date from user as u join "
				+ "(select m.id,m.fromUserid,m.fromContent,m.toname,m.date from mail as m"
				+ " where m.toname = " +"'"+ toname+"'"
				+ " and m.reading = 'yes') as a where u.id = a.fromUserid order by a.id desc limit "
				+ (page-1) * (titleCount) + "," + titleCount + "";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);
			while (result.next()) {
				MailBean m = new MailBean();
				m.setId(result.getInt(1));
				m.setFromUserid(result.getInt(2));
				m.setName(result.getString(3));
				m.setDisplayname(result.getString(4));
				m.setFromContent(result.getString(5));
				m.setToname(result.getString(6));
				m.setDate(result.getDate(7));
				list.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public ArrayList<MailBean> getAdminhaveRead(String toname,int page,int titleCount) {
		ArrayList<MailBean> list = new ArrayList<MailBean>();
		String sql = "select id,fromUserid,fromContent,date from mail where toname="+"'"+ toname+"'"
				+" and reading = 'yes' and fromUserid=0 order by id desc limit "
				+ (page-1) * (titleCount) + "," + titleCount + "";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);
			while (result.next()) {
				MailBean m = new MailBean();
				m.setId(result.getInt(1));
				m.setFromUserid(result.getInt(2));
				m.setFromContent(result.getString(3));
				m.setDate(result.getDate(4));
				list.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public ArrayList<MailBean> getDontHaveRead(String toname ,int page,int titleCount) {
		ArrayList<MailBean> list = new ArrayList<MailBean>();
		String sql = "select a.id,u.id,u.name,u.displayname,a.fromContent,a.toname,a.date from user as u join "
				+ "(select m.id,m.fromUserid,m.fromContent,m.toname,m.date from mail as m"
				+ " where m.toname =" +"'"+ toname+"'"
				+ " and m.reading = 'no') as a where u.id = a.fromUserid order by a.id desc limit "
				+ (page-1) * (titleCount) + "," + titleCount + "";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);
			while (result.next()) {
				MailBean m = new MailBean();
				m.setId(result.getInt(1));
				m.setFromUserid(result.getInt(2));
				m.setName(result.getString(3));
				m.setDisplayname(result.getString(4));
				m.setFromContent(result.getString(5));
				m.setToname(result.getString(6));
				m.setDate(result.getDate(7));
				list.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public ArrayList<MailBean> getAdminDontRead(String toname,int page,int titleCount) {
		ArrayList<MailBean> list = new ArrayList<MailBean>();
		String sql = "select id,fromUserid,fromContent,date from mail where toname="+"'"+ toname+"'"
				+" and reading = 'no' and fromUserid=0 order by id desc limit "
				+ (page-1) * (titleCount) + "," + titleCount + "";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);
			while (result.next()) {
				MailBean m = new MailBean();
				m.setId(result.getInt(1));
				m.setFromUserid(result.getInt(2));
				m.setFromContent(result.getString(3));
				m.setDate(result.getDate(4));
				list.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public MailBean getMailContent(int id) {
		MailBean m = new MailBean();
		String sql = "select u.id,u.name,u.displayname,a.fromContent,a.date from user as u join "
				+"(select m.id,m.fromUserid,m.fromContent,m.date from mail as m where id=?) as a"
				+" where u.id = a.fromUserid";
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			result = ps.executeQuery();
			if (!result.next())
				m = null;
			else {
				m.setId(result.getInt(1));
				m.setName(result.getString(2));
				m.setDisplayname(result.getString(3));
				m.setFromContent(result.getString(4));
				m.setDate(result.getDate(5));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return m;
	}
	public MailBean getadminContent(int id) {
		MailBean m = new MailBean();
		String sql = "select id,fromContent,date from mail where fromUserid = 0 and id = ?";
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			result = ps.executeQuery();
			if (!result.next())
				m = null;
			else {
				m.setId(result.getInt(1));
				m.setFromContent(result.getString(2));
				m.setDate(result.getDate(3));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return m;
	}
	public void updatemail(int id) {
		String sql = "update mail set reading='yes' where id = ?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public int getMailCount(String toname) {
		String sql = "select count(*) from mail where toname =? and reading = 'no' ";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, toname);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				this.mailNum=(result.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return mailNum ;
	}
	public int getCount(int titleCount,String toname) {
		String sql = "select count(*) from mail  where toname =? and reading = 'yes'";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, toname);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				if(result.getInt(1)%titleCount==0){
					this.PageCount=(result.getInt(1)/titleCount);
				}else{
					this.PageCount=(result.getInt(1)/titleCount+1);	
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return PageCount ;
	}
	public int getadminCount(int titleCount) {
		String sql = "select count(*) from mail  where fromUserid = 0  and reading = 'yes'";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				if(result.getInt(1)%titleCount==0){
					this.PageCount=(result.getInt(1)/titleCount);
				}else{
					this.PageCount=(result.getInt(1)/titleCount+1);	
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return PageCount ;
	}
	public int getNotCount(int titleCount,String toname) {
		String sql = "select count(*) from mail  where toname =? and reading = 'no'";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, toname);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				if(result.getInt(1)%titleCount==0){
					this.PageCount=(result.getInt(1)/titleCount);
				}else{
					this.PageCount=(result.getInt(1)/titleCount+1);	
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return PageCount ;
	}
	public void deleteMail(String toname) {
		String sql = "delete from mail where toname = ?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setString(1,toname);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void deleteMailAdmin() {
		String sql = "delete from mail where fromUserid = 0";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void setCon(Connection con) {
		this.con = con;
	}
}
